﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using System.Collections;
using System.IO;


namespace SimpleLibrary
{
    public class SqlLoadGen
    {
        // Initialize Configuration Sections
        public static void Init()
        {
            var SqlLoadGenSection = ConfigurationManager.GetSection("SqlLoadGen") as SqlLoadGenSection;

            SqlLoadGen.numberOfWorker = Convert.ToInt32(SqlLoadGenSection.Worker);
            SqlLoadGen.timeOut = Convert.ToInt32(SqlLoadGenSection.TimeOut);

            int iPercentage = 0;

            foreach (DatabaseElement database in SqlLoadGenSection.Elements)
            {
                string connKey = database.ConnectionStringKey;
                string connectionString = ConfigurationManager.ConnectionStrings[connKey].ConnectionString;

                foreach (TaskElement task in database.TaskElements)
                {
                    // if the task's hit percentage is "0" then just skip the task.
                    if (task.Percentage == 0)
                    {
                        continue;
                    }

                    SQLTask sqlTask = new SQLTask();
                    sqlTask.name = task.Name;
                    sqlTask.connectionString = connectionString;
                    sqlTask.startRange = iPercentage;
                    sqlTask.endRange = iPercentage + task.Percentage;
                    iPercentage = sqlTask.endRange;
                    
                    if (task.Transaction.ToLower() == "required")
                    {
                        sqlTask.txOption = TransactionScopeOption.Required;
                    }else
                    {
                        sqlTask.txOption = TransactionScopeOption.Suppress;
                    }

                    // Create SqlCommand 
                    foreach(JobElement job in task.JobElements){
                        SqlCommand comm = new SqlCommand(job.Query);
                        if (job.Type.ToLower() == "sp")
                        {
                            comm.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            comm.CommandType = CommandType.Text;
                        }

                        string strDataFile = job.DataSource;

                        // if there's no data file(in the case there's only autogen field exists), skip it.
                        if (string.IsNullOrEmpty(strDataFile) == false)
                        {
                            myFiles.Add(strDataFile);
                        }

                        // SQL Parameter Binding
                        foreach (ParamenterElement parameter in job.ParameterElements)
                        {
                            SqlParameter param = new SqlParameter();
                            param.ParameterName = parameter.Name;
                            param.SqlDbType = ConvertToDBType(parameter.Type);
                            string strDataKey = parameter.DataSourceKey;
                            comm.Parameters.Add(param);
                            // for autogen field
                            if (strDataKey.ToLower() == "autogen")
                            {
                                int seedkey = parameter.SeedKey;
                                int increment = parameter.Increment;

                                KeyGenerator.insertKey(job.Query + parameter.Name.Replace("@", ""),seedkey,increment);
                            }

                            mySource.Add(job.Query + parameter.Name, strDataFile + "|" + strDataKey);
                        }

                        sqlTask.commands.Add(comm);
                        
                    }
                    mySqlTask.Add(sqlTask);
                }

            }
        }

        // Convert string to SqlDbType
        public static SqlDbType ConvertToDBType(string strType)
        {
            string sType = strType.ToLower();
            switch (sType)
            {
                case "bigint": return SqlDbType.Binary; 
                case "binary":
                case "varbinary": return SqlDbType.VarBinary;
                case "bit": return SqlDbType.Bit;
                case "char": return SqlDbType.Char;
                case "date": return SqlDbType.Date;
                case "datetime": 
                case "smalldatetime": return SqlDbType.DateTime;
                case "datetime2": return SqlDbType.DateTime2;
                case "datetimeoffset": return SqlDbType.DateTimeOffset;
                case "float": return SqlDbType.Float;
                case "image": return SqlDbType.Image;
                case "int": return SqlDbType.Int;
                case "money": return SqlDbType.Money;
                case "nchar": return SqlDbType.NChar;
                case "ntext": return SqlDbType.NText;
                case "numeric": 
                case "decimal": return SqlDbType.Decimal;
                case "nvarchar": return SqlDbType.NVarChar;
                case "real": return SqlDbType.Real;
                case "rowversion": 
                case "timestamp" :return SqlDbType.Timestamp;
                case "smallint": return SqlDbType.SmallInt;
                case "smallmoney": return SqlDbType.SmallMoney;
                case "variant" :
                case "sql_variant": return SqlDbType.Variant;
                case "text": return SqlDbType.Text;
                case "time": return SqlDbType.Time;
                case "tinyint": return SqlDbType.TinyInt;
                case "uniqueidentifier": return SqlDbType.UniqueIdentifier;
                case "varchar": return SqlDbType.VarChar;
                case "xml": return SqlDbType.Xml;
                default: return SqlDbType.NVarChar;
            }
        }


        #region Load Data From csv files
        public static DataSet dsCSVs = new DataSet();

        public static void InintCSVFiles()
        {
            if (SqlLoadGen.myFiles.Count == 0)
            {
                Console.WriteLine("....... No CSV Files");
            }

            // fill dataset from csv file
            foreach (string strPath in SqlLoadGen.myFiles)
            {
                using (StreamReader readFile = new StreamReader(strPath))
                {
                    string line;
                    string[] row;

                    bool isHeader = true;
                    DataTable dtCsv = new DataTable();
                    dtCsv.TableName = strPath;
                    while ((line = readFile.ReadLine()) != null)
                    {
                        if (isHeader)
                        {
                            row = line.Split(',');
                            foreach (string strheader in row)
                            {
                                dtCsv.Columns.Add(strheader);
                            }
                            isHeader = false;
                        }
                        else
                        {
                            row = line.Split(',');
                            DataRow dr = dtCsv.NewRow();
                            for (int i = 0; i < dtCsv.Columns.Count; i++)
                            {
                                dr[i] = row[i];
                            }
                            dtCsv.Rows.Add(dr);
                        }
                        dtCsv.AcceptChanges();
                    }

                    try
                    {
                        dsCSVs.Tables.Add(dtCsv);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Duplicated datasource file name is not supported");
                        throw new Exception("Exit the Application");
                    }
                }
            }
        }

        #endregion

        // number of worker
        public static int numberOfWorker = 0;

        // timeout 
        public static int timeOut = 0;

        // Task Object contains SqlCommands to execute
        public static List<SQLTask> mySqlTask = new List<SQLTask>();

        // This object Contains DataSource File and Column Name
        public static Hashtable mySource = new Hashtable();

        // List of Data Files
        public static List<string> myFiles = new List<string>();
    }

    // Sql Task Object 
    public class SQLTask
    {
        public string name;
        public string connectionString;
        public int startRange;
        public int endRange;
        public TransactionScopeOption txOption;

        public List<SqlCommand> commands = new List<SqlCommand>();

        public SQLTask CopyTask()
        {
            SQLTask tempTask = new SQLTask();
            tempTask.name = this.name;
            tempTask.connectionString = this.connectionString;
            tempTask.txOption = this.txOption;
            tempTask.commands = new List<SqlCommand>();

            foreach(SqlCommand comm in this.commands){
                SqlCommand tempCommand = new SqlCommand();
                tempCommand.CommandText = comm.CommandText;
                tempCommand.CommandType = comm.CommandType;
                
                foreach(SqlParameter param in comm.Parameters){
                    SqlParameter tempParameter = new SqlParameter();
                    tempParameter.ParameterName = param.ParameterName;
                    tempParameter.SqlDbType = param.SqlDbType;
                    tempCommand.Parameters.Add(tempParameter);
                }

                tempTask.commands.Add(tempCommand);
            }

            return tempTask;
        }
    }

    // Key generator for "autogen" column
    public class KeyGenerator
    {
        static Hashtable htKeyGen = new Hashtable();
        // insertKey method accessed sequentially. 
        public static void insertKey(string command,int seed,int increment)
        {
            AutoGenInfo autogen = new AutoGenInfo();
            autogen.seedKey = seed;
            autogen.increment = increment;
            htKeyGen.Add(command, autogen);
        }

        // getKey method is being accessed simultaneously
        public static int getKey(string command){
            lock (htKeyGen)
            {
                AutoGenInfo autogen = (AutoGenInfo)htKeyGen[command];
                int iKey = autogen.seedKey;
                autogen.seedKey = autogen.seedKey + autogen.increment;
                htKeyGen[command] = autogen;
                return iKey;
            }
        }
    }

    public class AutoGenInfo
    {
        public int seedKey = 0;
        public int increment = 1;
    }
}
